package org.totoro.querydsl;

import com.querydsl.core.types.Order;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.dsl.CaseBuilder;
import com.querydsl.core.types.dsl.NumberExpression;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.totoro.entity.QUserInfo;
import org.totoro.entity.QUserInfoExt;
import org.totoro.entity.UserInfo;

import java.util.List;

/**
 * @author YHL
 * @version V1.0
 * @Description:
 * @date 2018-08-07
 * @see https://www.jianshu.com/p/69dcb1b85bbb
 * @see https://blog.csdn.net/liuchuanhong1/article/details/70244261?utm_source=gold_browser_extension
 * @see https://blog.csdn.net/wujiaqi0921/article/details/78789087
 * @see https://blog.csdn.net/qq_30054997/article/details/79420141
 */
@Service
public class UserDslService {


    @Autowired
    private JPAQueryFactory jpaQueryFactory;

    /**
     * join 查询
     *
     * @return
     */

    public UserInfo joinQuery() {


        QUserInfo userInfo = QUserInfo.userInfo;


        /**
         * select userinfo0_.id          as id1_1_,
         *        userinfo0_.email       as email2_1_,
         *        userinfo0_.name        as name3_1_,
         *        userinfo0_.password    as password4_1_,
         *        userinfo0_.user_ext_id as user_ext5_1_
         * from user_info userinfo0_
         *        left outer join user_info_ext userinfoex1_ on userinfo0_.user_ext_id = userinfoex1_.id;
         */
        List<UserInfo> fetch = jpaQueryFactory.selectFrom(userInfo)
                .leftJoin(userInfo.userInfoExt, QUserInfoExt.userInfoExt)
                .fetch();


        /**
         *select userinfoex0_.id as id1_2_, userinfoex0_.addr as addr2_2_, userinfoex0_.user_id as user_id3_2_
         * from user_info_ext userinfoex0_
         *        left outer join user_info userinfo1_ on userinfoex0_.user_id = userinfo1_.id
         */
        QUserInfoExt userInfoExt = QUserInfoExt.userInfoExt;
//
//        jpaQueryFactory.selectFrom(userInfoExt)
//                .leftJoin(userInfoExt.userInfo, QUserInfo.userInfo)
//                .fetch();


//        CaseBuilder caseBuilder = new CaseBuilder();
//        caseBuilder.when(null).then("");
//        caseBuilder.when(null).then(0);


        return null;

    }

    /**
     * 分组查询
     */
    public void groupQuery() {

        QUserInfo userInfo = QUserInfo.userInfo;

        jpaQueryFactory.select(userInfo.name).from(userInfo)
                .leftJoin(userInfo.userInfoExt, QUserInfoExt.userInfoExt)
                .groupBy(userInfo.name)
                .fetch();
    }


    /**
     * caseWhenSort 排序查询
     */
    public void CaseWhenSort() {

        QUserInfo userInfo = QUserInfo.userInfo;
        /**
         * case when 排序
         */
        CaseBuilder caseBuilder = new CaseBuilder();
        NumberExpression<Integer> otherwise = caseBuilder.when(userInfo.id.gt(50)).then(1)
                .otherwise(0);

        jpaQueryFactory.select(userInfo).from(userInfo)
                .leftJoin(userInfo.userInfoExt, QUserInfoExt.userInfoExt)
                .orderBy(userInfo.name.desc(), new OrderSpecifier(Order.ASC, otherwise))
                .fetch();

    }

    /**
     * caseWhenQuery 查询
     */
    public void caseWhenQuery() {


        /**
         * select case when userinfo0_.id > ? then ? else 0 end as col_0_0_
         * from user_info userinfo0_
         *        left outer join user_info_ext userinfoex1_ on userinfo0_.user_ext_id = userinfoex1_.id
         * order by userinfo0_.name desc, case when userinfo0_.id > ? then ? else 0 end asc
         */

        QUserInfo userInfo = QUserInfo.userInfo;
        /**
         * case when 排序
         */
        CaseBuilder caseBuilder = new CaseBuilder();

        NumberExpression<Integer> otherwise = caseBuilder.when(userInfo.id.gt(50)).then(1)
                .otherwise(0);

        jpaQueryFactory.select(otherwise).from(userInfo)
                .leftJoin(userInfo.userInfoExt, QUserInfoExt.userInfoExt)
                .orderBy(userInfo.name.desc(), new OrderSpecifier(Order.ASC, otherwise))
                .fetch();

    }

    /**
     * 自定义  返回 bean 结果
     */
    public void customResultBeanQuery() {

        QUserInfo userInfo = QUserInfo.userInfo;

        //userInfo.name.as("userName")  取别名

        jpaQueryFactory.select(Projections.bean(UserInfo.class, userInfo.id, userInfo.name.as("userName"))).from(userInfo)
                .fetch();

    }

    /**
     * 分页查询
     */
    public void page() {

        QUserInfo userInfo = QUserInfo.userInfo;

        jpaQueryFactory.select(Projections.bean(UserInfo.class, userInfo.id, userInfo.name)).from(userInfo)
                .offset(10).limit(20)
                .fetch();
    }


}
